USE [WEALTHDB]
GO

/****** Object:  Table [dbo].[ziChanSetting]    Script Date: 03/02/2013 22:41:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ziChanSetting](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[txtName] [nvarchar](50) NULL,
	[txtPhone] [nvarchar](50) NULL,
	[txtHealth] [nvarchar](500) NULL,
	[txtCost] [nvarchar](500) NULL,
	[drpWorkType] [nvarchar](50) NULL,
	[isMyComp] [bit] NULL,
	[moneyFrom] [nvarchar](500) NULL,
	[txtMoney] [nvarchar](100) NULL,
	[drpHomeCost] [nvarchar](50) NULL,
	[txtTZFX] [nvarchar](100) NULL,
	[agoBuyList] [nvarchar](500) NULL,
	[tuoZiGuanZhu] [nvarchar](500) NULL,
	[ziXunFrom] [nvarchar](500) NULL,
	[txtTotalMoney] [nvarchar](100) NULL,
	[txtJYBaozhang] [nvarchar](100) NULL,
	[txtGoalEndData] [nvarchar](50) NULL,
	[txtGoalDesc] [nvarchar](100) NULL,
	[txtGoalMoney] [nvarchar](50) NULL,
	[txtGoalDesc2] [nvarchar](50) NULL,
	[yearInMoney] [nvarchar](50) NULL,
	[yearOutMoney] [nvarchar](50) NULL,
	[txtGZ] [nvarchar](50) NULL,
	[txtKX] [nvarchar](50) NULL,
	[txtWifeMoney] [nvarchar](50) NULL,
	[txtHospital] [nvarchar](50) NULL,
	[txtPrize] [nvarchar](50) NULL,
	[txtEducation] [nvarchar](50) NULL,
	[txtLiXi] [nvarchar](50) NULL,
	[txtBX] [nvarchar](50) NULL,
	[txtRent] [nvarchar](50) NULL,
	[txtQTZC] [nvarchar](50) NULL,
	[txtQTSR] [nvarchar](50) NULL,
	[txtPayRate] [nvarchar](50) NULL,
	[txtSRRate] [nvarchar](50) NULL,
	[ziChan] [nvarchar](50) NULL,
	[fuZhai] [nvarchar](50) NULL,
	[txtCushType] [nvarchar](50) NULL,
	[txtGJJ] [nvarchar](50) NULL,
	[txtGDSY] [nvarchar](50) NULL,
	[txtGRJD] [nvarchar](50) NULL,
	[txtFDSY] [nvarchar](50) NULL,
	[txtSYZF] [nvarchar](50) NULL,
	[txtBDC] [nvarchar](50) NULL,
	[txtQTFZ] [nvarchar](50) NULL,
	[txtBXType] [nvarchar](50) NULL,
	[txtQTZCType] [nvarchar](100) NULL,
	[txtKTZGM] [nvarchar](100) NULL,
	[createDateTime] [datetime] NULL,
	[memo] [nvarchar](2000) NULL,
	[status] [int] NULL,
	[updateDatetime] [datetime] NULL,
 CONSTRAINT [PK_ziChanSetting] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [WEALTHDB]
GO

/****** Object:  StoredProcedure [dbo].[Get_ziChanSetting_all]    Script Date: 03/02/2013 22:41:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<mate>
-- Create date: <2013,01,11>
-- Description:	<UserList>
-- =============================================
CREATE PROCEDURE [dbo].[Get_ziChanSetting_all]
	-- Add the parameters for the stored procedure here
	@txtName varchar(50) = null,
	@status varchar(50) = null
	
AS
DECLARE @SQL AS VARCHAR(2000);
set @SQL = 'SELECT [ziChanSetting].[id]
      ,[txtName]
      ,[txtPhone]
      ,[txtHealth]
      ,[txtCost]
      ,[drpWorkType]
      ,[isMyComp]
      ,[moneyFrom]
      ,[txtMoney]
      ,[drpHomeCost]
      ,[txtTZFX]
      ,[agoBuyList]
      ,[tuoZiGuanZhu]
      ,[ziXunFrom]
      ,[txtTotalMoney]
      ,[txtJYBaozhang]
      ,[txtGoalEndData]
      ,[txtGoalDesc]
      ,[txtGoalMoney]
      ,[txtGoalDesc2]
      ,[yearInMoney]
      ,[yearOutMoney]
      ,[txtGZ]
      ,[txtKX]
      ,[txtWifeMoney]
      ,[txtHospital]
      ,[txtPrize]
      ,[txtEducation]
      ,[txtLiXi]
      ,[txtBX]
      ,[txtRent]
      ,[txtQTZC]
      ,[txtQTSR]
      ,[txtPayRate]
      ,[txtSRRate]
      ,[ziChan]
      ,[fuZhai]
      ,[txtCushType]
      ,[txtGJJ]
      ,[txtGDSY]
      ,[txtGRJD]
      ,[txtFDSY]
      ,[txtSYZF]
      ,[txtBDC]
      ,[txtQTFZ]
      ,[txtBXType]
      ,[txtQTZCType]
      ,[txtKTZGM]
      ,[createDateTime],
      [memo],
      [updateDateTime]
  FROM [dbo].[ziChanSetting]
  WHERE 1=1'
		

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF @txtName is not null and @txtName !=''
		SET @sql  = @sql + ' AND [txtName] LIKE ''%'+@txtName+'%''';
	IF @status is not null and @status != ''
		EXEC(@sql + ' AND [status] = '+@status +' ORDER BY [createDateTime] DESC')
	ELSE
		EXEC(@sql + 'ORDER BY [createDateTime] DESC');
	
END

GO

USE [WEALTHDB]
GO

/****** Object:  StoredProcedure [dbo].[Get_ziChanSetting_Delete]    Script Date: 03/02/2013 22:41:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<mate>
-- Create date: <2013.02.10>
-- Description:	<Login>
-- =============================================
create PROCEDURE [dbo].[Get_ziChanSetting_Delete]
	-- Add the parameters for the stored procedure here		
		@ID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DELETE [ziChanSetting] WHERE ID =@ID
END


GO

USE [WEALTHDB]
GO

/****** Object:  StoredProcedure [dbo].[Get_ziChanSetting_insert]    Script Date: 03/02/2013 22:42:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<mate>
-- Create date: <2013.02.10>
-- Description:	<Login>
-- =============================================
CREATE PROCEDURE [dbo].[Get_ziChanSetting_insert]
	-- Add the parameters for the stored procedure here
	@txtName nvarchar(50),
	@txtPhone nvarchar(50),
	@txtHealth nvarchar(500),
	@txtCost nvarchar(500),
	@drpWorkType nvarchar(50),
	@isMyComp bit,
	@moneyFrom nvarchar(500),
	@txtMoney nvarchar(100),
	@drpHomeCost nvarchar(50),
	@txtTZFX nvarchar(100),
	@agoBuyList nvarchar(500),
	@tuoZiGuanZhu nvarchar(500),
	@ziXunFrom nvarchar(500),
	@txtTotalMoney nvarchar(100),
	@txtJYBaozhang nvarchar(100),
	@txtGoalEndData nvarchar(50),
	@txtGoalDesc nvarchar(100),
	@txtGoalMoney nvarchar(50),
	@txtGoalDesc2 nvarchar(50),
	@yearInMoney nvarchar(50),
	@yearOutMoney nvarchar(50),
	@txtGZ nvarchar(50),
	@txtKX nvarchar(50),
	@txtWifeMoney nvarchar(50),
	@txtHospital nvarchar(50),
	@txtPrize nvarchar(50),
	@txtEducation nvarchar(50),
	@txtLiXi nvarchar(50),
	@txtBX nvarchar(50),
	@txtRent nvarchar(50),
	@txtQTZC nvarchar(50),
	@txtQTSR nvarchar(50),
	@txtPayRate nvarchar(50),
	@txtSRRate nvarchar(50),
	@ziChan nvarchar(50),
	@fuZhai nvarchar(50),
	@txtCushType nvarchar(50),
	@txtGJJ nvarchar(50),
	@txtGDSY nvarchar(50),
	@txtGRJD nvarchar(50),
	@txtFDSY nvarchar(50),
	@txtSYZF nvarchar(50),
	@txtBDC nvarchar(50),
	@txtQTFZ nvarchar(50),
	@txtBXType nvarchar(50),
	@txtQTZCType nvarchar(100),
	@txtKTZGM nvarchar(100),
	@memo nvarchar(200)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [WEALTHDB].[dbo].[Get_ziChanSetting_insert]
           ([txtName]
      ,[txtPhone]
      ,[txtHealth]
      ,[txtCost]
      ,[drpWorkType]
      ,[isMyComp]
      ,[moneyFrom]
      ,[txtMoney]
      ,[drpHomeCost]
      ,[txtTZFX]
      ,[agoBuyList]
      ,[tuoZiGuanZhu]
      ,[ziXunFrom]
      ,[txtTotalMoney]
      ,[txtJYBaozhang]
      ,[txtGoalEndData]
      ,[txtGoalDesc]
      ,[txtGoalMoney]
      ,[txtGoalDesc2]
      ,[yearInMoney]
      ,[yearOutMoney]
      ,[txtGZ]
      ,[txtKX]
      ,[txtWifeMoney]
      ,[txtHospital]
      ,[txtPrize]
      ,[txtEducation]
      ,[txtLiXi]
      ,[txtBX]
      ,[txtRent]
      ,[txtQTZC]
      ,[txtQTSR]
      ,[txtPayRate]
      ,[txtSRRate]
      ,[ziChan]
      ,[fuZhai]
      ,[txtCushType]
      ,[txtGJJ]
      ,[txtGDSY]
      ,[txtGRJD]
      ,[txtFDSY]
      ,[txtSYZF]
      ,[txtBDC]
      ,[txtQTFZ]
      ,[txtBXType]
      ,[txtQTZCType]
      ,[txtKTZGM]
      ,[createDateTime],
      [memo],
      [status],
      [datetime])
     VALUES(
           @txtName,
	@txtPhone,
	@txtHealth,
	@txtCost,
	@drpWorkType,
	@isMyComp,
	@moneyFrom,
	@txtMoney,
	@drpHomeCost,
	@txtTZFX,
	@agoBuyList,
	@tuoZiGuanZhu,
	@ziXunFrom,
	@txtTotalMoney,
	@txtJYBaozhang,
	@txtGoalEndData,
	@txtGoalDesc,
	@txtGoalMoney,
	@txtGoalDesc2,
	@yearInMoney,
	@yearOutMoney,
	@txtGZ,
	@txtKX,
	@txtWifeMoney,
	@txtHospital,
	@txtPrize,
	@txtEducation,
	@txtLiXi,
	@txtBX,
	@txtRent,
	@txtQTZC,
	@txtQTSR,
	@txtPayRate,
	@txtSRRate,
	@ziChan,
	@fuZhai,
	@txtCushType,
	@txtGJJ,
	@txtGDSY,
	@txtGRJD,
	@txtFDSY,
	@txtSYZF,
	@txtBDC,
	@txtQTFZ,
	@txtBXType,
	@txtQTZCType,
	@txtKTZGM,
	getdate(),
	@memo,
	'0',
	getdate())
END


GO

USE [WEALTHDB]
GO

/****** Object:  StoredProcedure [dbo].[Get_ziChanSetting_Update]    Script Date: 03/02/2013 22:42:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<mate>
-- Create date: <2013.02.10>
-- Description:	<Login>
-- =============================================
create PROCEDURE [dbo].[Get_ziChanSetting_Update]
	-- Add the parameters for the stored procedure here
	@id int,
	@status int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	UPDATE [WEALTHDB].[dbo].[ziChanSetting]
   SET [status] = @status
      ,[updateDateTime] = getdate()
 WHERE id = @id
END


GO

